package com.wonsec.useragent.repository;

import com.wonsec.useragent.entity.ChannelTracking;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.LocalDateTime;
import java.util.List;

/**
 * @Author： mzy
 * @Date： 2025/5/13 下午2:53
 * @Describe：
 */
public interface ChannelTrackingRepository extends JpaRepository<ChannelTracking, Long> {

    @Query("SELECT c.channel, COUNT(c) FROM ChannelTracking c GROUP BY c.channel")
    List<Object[]> countByChannel();

    @Query("SELECT DATE(c.accessTime) as day, c.channel, COUNT(c) " +
            "FROM ChannelTracking c " +
            "WHERE c.accessTime >= :startDate AND c.accessTime <= :endDate " +
            "GROUP BY day, c.channel " +
            "ORDER BY day")
    List<Object[]> countByDateAndChannel(@Param("startDate") LocalDateTime startDate,
                                         @Param("endDate") LocalDateTime endDate);
}
